package com.ipan.poi.excel.service;

import java.beans.PropertyDescriptor;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.beanutils.PropertyUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.ipan.poi.PoiConfig;
import com.ipan.poi.excel.config.XlsEntity;
import com.ipan.poi.excel.config.XlsProperty;
import com.ipan.poi.excel.util.SqlUtils;
import com.ipan.poi.jdbc.JDBCConfig;
import com.ipan.poi.jdbc.JDBCManager;
import com.ipan.poi.utils.PoiBeanHelper;

/**
 * 数据库操作实现类；
 * 默认实现只支持ID自动编号的实体类；
 * 
 * @author iPan
 * @version 2013-09-20
 */
public class DefaultXlsService implements XlsService<Object> {

	protected Logger logger = LoggerFactory.getLogger(getClass());

	public void insertXls(final XlsEntity defEntity, final Object entity) {
		String sql = SqlUtils.createInsertSql(defEntity, entity);
		Object[] params = SqlUtils.createInsertParams(defEntity, entity);
		Class<?> objClass = entity.getClass();
		update(sql, params, objClass);
	}

	public void updateXls(final XlsEntity defEntity, final Object entity) {
		String sql = SqlUtils.createUpdateSql(defEntity, entity);
		Object[] params = SqlUtils.createUpdateParams(defEntity, entity);
		Class<?> objClass = entity.getClass();
		update(sql, params, objClass);
	}

	public List<Object> searchXls(final XlsEntity defEntity, final Object entity) {
		String sql = SqlUtils.createSearchSql(defEntity, entity);
		Object[] params = SqlUtils.createSearchParams(defEntity, entity);
		Class<?> objClass = entity.getClass();
		List<Object> result = query(sql, params, objClass);
		return result;
	}

	public List<Object> selectXls(final XlsEntity defEntity, final Object entity) {
		String sql = SqlUtils.createSelectSql(defEntity, entity);
		Object[] params = SqlUtils.createSelectParams(defEntity, entity);
		Class<?> objClass = entity.getClass();
		List<Object> result = query(sql, params, objClass);
		return result;
	}

	protected void update(String sql, Object[] params, Class<?> objClass) {
		JDBCConfig jdbcConfig = PoiConfig.getInstance().getJDBCConfig();
		Connection conn = null;
		try {
			conn = JDBCManager.createConnection(jdbcConfig);
			logger.info("XlsService: {}", sql);
			JDBCManager.ExecuteUpdate(conn, sql, params);
		} finally {
			JDBCManager.closeConnection(conn);
		}
	}

	protected List<Object> query(String sql, Object[] params, Class<?> objClass) {
		JDBCConfig jdbcConfig = PoiConfig.getInstance().getJDBCConfig();
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet resultset = null;
		List<Object> objList = new ArrayList<Object>();
		try {
			conn = JDBCManager.createConnection(jdbcConfig);
			statement = conn.prepareStatement(sql);
			logger.info("XlsService: {}", sql);
			resultset = JDBCManager.ExecuteQuery(statement, sql, params);
			while (resultset.next()) {
				Object newObj = objClass.newInstance();
				resultSetToEntity(resultset, newObj);
				objList.add(newObj);
			}
		} catch (Exception e) {
			JDBCManager.throwJDBCException(e);
		} finally {
			JDBCManager.closeResultSet(resultset);
			JDBCManager.closeStatement(statement);
			JDBCManager.closeConnection(conn);
		}
		return objList;
	}

	protected boolean isValidatable(XlsEntity defEntity) {
		List<XlsProperty> list = defEntity.getValidProperty();
		return (list != null && list.size() > 0) ? true : false;
	}

	protected void resultSetToEntity(ResultSet resultset, Object entity) {
		PropertyDescriptor[] property = PropertyUtils.getPropertyDescriptors(entity.getClass());
		for (PropertyDescriptor p : property) {
			String fieldName = p.getName();
			try {
				resultset.findColumn(fieldName);
			} catch (Exception e) {
				continue;
			}
			try {
				PoiBeanHelper.setBeanValueOfAuto(entity, fieldName, resultset.getObject(fieldName));
			} catch (Exception e) {
				logger.warn("为实体[{}]设置属性[{}]出错！", entity.getClass().getName(), fieldName);
			}
		}
	}

}
